package com.hsit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;

@Repository
public class TreeDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;


	public List parentList() {
		String sql = "SELECT t.id,t.text,(SELECT COUNT(*) FROM tree WHERE fatherid=t.id) AS TOATL FROM tree t where fatherid is NULL";
		final List<EasyUiTree> list = new ArrayList<EasyUiTree>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				EasyUiTree easyUiTree = new EasyUiTree();
				easyUiTree.setId(rs.getInt("id"));
				easyUiTree.setName(rs.getString("text"));
				int total = rs.getInt("TOATL");
				if (total > 0) {
					easyUiTree.setState("closed");
				}
				list.add(easyUiTree);
			}
		});
		return list;
	}

	
	public List pp(){
		String sql=" SELECT * FROM tree";
		return jdbcTemplate.queryForList(sql);
	}
	public List pp3(int id){
		String sql=" SELECT * FROM tree where fatherid=? ";
		return jdbcTemplate.queryForList(sql,new Object[]{id});
	}
	
	
	public List<EasyUiTree> childrenList(int id){
		String sql ="SELECT t.id,t.text,(SELECT COUNT(*) FROM tree WHERE fatherid=t.id) AS TOATL FROM tree t where fatherid=?";
		final List<EasyUiTree> list =new ArrayList<EasyUiTree>();
		jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				EasyUiTree easyUiTree=new EasyUiTree();
				easyUiTree.setId(rs.getInt("id"));
				easyUiTree.setName(rs.getString("text"));
				int count=rs.getInt("TOATL");
				if (count>0) {
					easyUiTree.setState("closed");
				}
				list.add(easyUiTree);
			}
		});
		return list;
	}
	
	
	
	public List parentList2() {
		String sql = "SELECT t.id,t.text,t.fatherid,(SELECT COUNT(*) FROM tree WHERE fatherid=t.id) AS TOATL FROM tree t where fatherid is NULL";
		final List<ExtTree> list = new ArrayList<ExtTree>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				ExtTree extTree = new ExtTree();
				extTree.setId(rs.getInt("id"));
				extTree.setText(rs.getString("text"));
				int total = rs.getInt("TOATL");
				if (total ==0) {
					extTree.setLeaf("true");
				}
				extTree.setChecked(false);
				list.add(extTree);
			}
		});
		return list;
	}

	
	
	
	
	public List<ExtTree> childrenList2(int id){
		String sql ="SELECT t.id,t.text,t.fatherid,(SELECT COUNT(*) FROM tree WHERE fatherid=t.id) AS TOATL FROM tree t where t.fatherid=?";
		final List<ExtTree> list =new ArrayList<ExtTree>();
		jdbcTemplate.query(sql, new Object[]{id}, new RowCallbackHandler() {
			
			@Override
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				ExtTree extTree=new ExtTree();
				extTree.setId(rs.getInt("id"));
				extTree.setText(rs.getString("text"));
				extTree.setChecked(true);
				int count=rs.getInt("TOATL");
				if (count==0) {
					extTree.setLeaf("true");
				}
				list.add(extTree);
			}
		});
		return list;
	}
}
